---
title: "pk72846_view_policy stored procedure documentation"
summary: "Retrieved Oracle package supplying view-policy detail, periods, addresses, gaps, team assignments, and affiliation helpers."
status: draft
owner: "SAIF Claims BOF team"
last_reviewed: "2026-08-25"
source_schema: "WCIS_DBA"
source_name: "pk72846_view_policy"
source_type: "PACKAGE"
retrieval_status: "found"
retrieval_command: "legends oracle query --name pk72846_view_policy"
retrieval_date: "2026-08-25"
---
# pk72846_view_policy
## Overview
| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Package |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsAgency`, `clsMCOCollection`, `clsPolicyMCOCollection`, `clsAffiliatedPolicyCollection`, `clsPolicyAddress`, `clsPolicyPeriodCollection`, `clsTeamAssignmentCollection`, `clsPolicyData` |
## Purpose
`pk72846_view_policy` is the libraries main read-model package for policy-view screens. The retrieved body returns policy summary/detail rows, agency and address data, team assignments, policy-period history, gap indicators, and several affiliation and wrap-up helper results that the library layers onto `clsPolicyData` and related collections.
## Procedures / Methods
### getviewdetail
| Field | Value |
| --- | --- |
| **Returns** | `viewpolicydetail_cursor` |
| **Called by** | `clsPolicyData` ad hoc retrieval |
| **Description** | Returns the primary view-policy summary row for a policy, including entity, classification, balance, premium, gap, group, reimbursement, sales-plan, vendor, and web-address data. |
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number to retrieve. |
| `recordset` | IN OUT | `viewpolicydetail_cursor` | — | Cursor populated with the summary detail row. |
### team_assign_query
| Field | Value |
| --- | --- |
| **Returns** | `team_assign_cursor` |
| **Called by** | `clsTeamAssignmentCollection.Criteria.RetrieveSQL` |
| **Description** | Returns ordered team-assignment rows plus synthesized office, customer-service-team, agency, and producer rows. |
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number to retrieve. |
| `recordset` | IN OUT | `team_assign_cursor` | — | Cursor populated with team-assignment rows. |
### getpolicyperiods
| Field | Value |
| --- | --- |
| **Returns** | `ref_cursor` |
| **Called by** | `clsPolicyPeriodCollection.Criteria.RetrieveSQL` |
| **Description** | Returns the policy-period list used by View Policy, including masking for assigned-risk rows and effective-dated plan and rating fields. |
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number to retrieve. |
| `atopnpolper` | IN | `NUMBER` | — | Positive values request all periods; zero keeps the default current-period behavior. |
| `recordset` | IN OUT | `ref_cursor` | — | Cursor populated with policy-period rows. |
### getpolicyaddress
| Field | Value |
| --- | --- |
| **Returns** | `ref_cursor` |
| **Called by** | `clsPolicyAddress.Criteria.RetrieveSQL` |
| **Description** | Returns the preferred current policy or claim address and contact row, with optional fallback from `CLM` to `POL`. |
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inpolicynum` | IN | `NUMBER` | — | Policy number to retrieve. |
| `inaddresstype` | IN | `VARCHAR2` | — | Requested location code, such as `CLM` or `POL`. |
| `incontacttype` | IN | `VARCHAR2` | — | Contact type code used when joining `CONTACT`. |
| `ingetmailaddrwhennotfound` | IN | `VARCHAR2` | — | `Y` enables claim-address fallback to `POL`. |
| `recordset` | IN OUT | `ref_cursor` | — | Cursor populated with the selected address row. |
### getpolicyaddressbylocseqnum
| Field | Value |
| --- | --- |
| **Returns** | `ref_cursor` |
| **Called by** | `clsPolicyAddress` ad hoc retrieval |
| **Description** | Returns the preferred address and contact row for an explicit location sequence number. |
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inlocationseqnum` | IN | `NUMBER` | — | Location sequence number to retrieve. |
| `recordset` | IN OUT | `ref_cursor` | — | Cursor populated with the selected address row. |
### getagency
| Field | Value |
| --- | --- |
| **Returns** | `agency_cursor` |
| **Called by** | `clsAgency.Criteria.RetrieveSQL` |
| **Description** | Returns agency and producer contact information for the current agent of record. |
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number to retrieve. |
| `recordset` | IN OUT | `agency_cursor` | — | Cursor populated with agency contact data. |
### getgaps
| Field | Value |
| --- | --- |
| **Returns** | `gap_cursor` |
| **Called by** | `clsPolicyData` ad hoc retrieval |
| **Description** | Returns coverage-gap rows derived from adjacent policy periods and explicit `POLICY_COVERAGE_GAP` rows. |
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number to inspect. |
| `recordset` | IN OUT | `gap_cursor` | — | Cursor populated with gap rows. |
### getpolicyaffiliatetype
| Field | Value |
| --- | --- |
| **Returns** | `VARCHAR2` |
| **Called by** | `clsPolicyData` obsolete helper |
| **Description** | Classifies the policy as a manual-affiliation, relationship, combination, premium-discount, or non-affiliated policy. |
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Policy number to classify. |
| `in_is_policyholder_user` | IN | `VARCHAR2` | — | `TRUE` enables manual policyholder-affiliation checks before other families. |
### getpolicyaffiliatelist
| Field | Value |
| --- | --- |
| **Returns** | `ref_cursor` |
| **Called by** | `clsAffiliatedPolicyCollection.Criteria.RetrieveSQL` |
| **Description** | Returns affiliated-policy members and an optional totals row for the requested affiliation family. |
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Anchor policy number. |
| `in_affiliate_type` | IN | `VARCHAR2` | — | Requested affiliation family. |
| `in_include_affil_totals` | IN | `VARCHAR2` | — | `TRUE` includes a totals row for supported families. |
| `recordset` | OUT | `ref_cursor` | — | Cursor populated with affiliate rows. |
### getparentpolicy
| Field | Value |
| --- | --- |
| **Returns** | `NUMBER` |
| **Called by** | `clsPolicyData` obsolete helper |
| **Description** | Returns the parent policy number that corresponds to the resolved affiliation type. |
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Policy number to evaluate. |
| `in_is_policyholder_user` | IN | `VARCHAR2` | — | `TRUE` enables manual policyholder-affiliation checks first. |
### getwrapuplegalname
| Field | Value |
| --- | --- |
| **Returns** | `VARCHAR2` |
| **Called by** | `clsPolicyData` ad hoc retrieval |
| **Description** | Returns the wrap-up legal name, escalating from the member row to the relationship policy and then the rate account when present. |
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Policy number to evaluate. |
## Business Rules
The retrieved database source embeds the following server-side rules.
| Rule ID | Summary |
| --- | --- |
| [BR-VP-018](#br-vp-018-gap-detection-ignores-flat-cancel-and-prospect-periods) | Coverage-gap detection excludes flat-cancel and `PPROS` periods before comparing adjacent periods. |
| [BR-VP-019](#br-vp-019-assigned-risk-policy-periods-mask-risk-type-and-plan-data-until-the-info-page-prints) | `RF` policy periods blank risk-type and plan output until `SP72347_INFO_PAGE_PRINTED_YN` returns `Y`. |
| [BR-VP-020](#br-vp-020-claim-address-requests-may-fall-back-to-the-policy-address) | When callers allow fallback and no active `CLM` location exists, address retrieval switches to `POL`. |
| [BR-VP-021](#br-vp-021-address-retrieval-prefers-current-contacts-and-the-latest-effective-location-row) | Address lookups limit results to active contacts and the most recent effective matching location, then return only the first ranked row. |
| [BR-VP-022](#br-vp-022-affiliate-type-classification-follows-a-fixed-hierarchy) | Affiliate detection checks policyholder or manual affiliations first, then relationship, combination, and premium-discount affiliations. |
| [BR-VP-023](#br-vp-023-parent-policy-resolution-depends-on-the-resolved-affiliate-type) | Parent-policy lookup chooses `policy_relationship`, `policy_relationship_policy_num`, `rate_account`, or premium-member logic based on affiliate type. |
| [BR-VP-024](#br-vp-024-affiliate-total-rows-are-optional-and-vary-by-affiliation-family) | Affiliate lists add a totals row only when `in_include_affil_totals = 'TRUE'`, using family-specific labels. |
| [BR-VP-025](#br-vp-025-wrap-up-legal-name-lookup-climbs-from-member-to-relationship-policy-to-rate-account) | Wrap-up legal-name resolution starts on the member row, then follows the relationship policy and rate account when present. |
### BR-VP-018 Gap detection ignores flat-cancel and prospect periods
| Field | Value |
| --- | --- |
| **Statement** | Gap detection ignores policy periods with status `FC` or `PPROS` before comparing one periods cancel or expiration date to the next periods effective date. |
| **Trigger** | `getgaps` or `getgapindicator` executes. |
| **Enforcement** | Both routines filter out `policy_period_status_code` values `FC` and `PPROS` and compare only the remaining periods. |
| **Outcome** | Only meaningful coverage gaps are returned or counted. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
---
### BR-VP-019 Assigned-risk policy periods mask risk-type and plan data until the info page prints
| Field | Value |
| --- | --- |
| **Statement** | For policy periods with status `RF`, the package suppresses `risk_type_code` and plan description values until `SP72347_INFO_PAGE_PRINTED_YN(policy_num, policy_period_eff_date)` returns `Y`. |
| **Trigger** | `getpolicyperiod` or `getpolicyperiods` retrieves an `RF` policy period. |
| **Enforcement** | Both procedures use `CASE` expressions that return empty strings for `RF` rows when `SP72347_INFO_PAGE_PRINTED_YN` is not `Y`. |
| **Outcome** | Assigned-risk policy-period detail stays masked until the information page is printed. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
---
### BR-VP-020 Claim-address requests may fall back to the policy address
| Field | Value |
| --- | --- |
| **Statement** | If a claim-address request finds no active claim location and `ingetmailaddrwhennotfound = 'Y'`, the procedure changes the requested location code to `POL` before opening the cursor. |
| **Trigger** | `getpolicyaddress` executes for address type `CLM` and no current `CLM` location exists. |
| **Enforcement** | The procedure counts qualifying `CLM` locations, then assigns `v_addr_type := 'POL'` when fallback is enabled and the count is zero. |
| **Outcome** | Callers receive the policy mailing or location address instead of an empty claim-address result. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
---
### BR-VP-021 Address retrieval prefers current contacts and the latest effective location row
| Field | Value |
| --- | --- |
| **Statement** | Policy-address lookups join only active contacts, constrain location rows to the latest qualifying effective date, and return a single top-ranked row; location-sequence lookups also rank contact types before applying `ROWNUM <= 1`. |
| **Trigger** | `getpolicyaddress` or `getpolicyaddressbylocseqnum` executes. |
| **Enforcement** | `getpolicyaddress` filters `contact_exp_date IS NULL`, constrains the effective date with a `MAX(loc_eff_date)` subquery, and limits the result with `ROWNUM <= 1`; `getpolicyaddressbylocseqnum` orders contact types and also applies `ROWNUM <= 1`. |
| **Outcome** | The package returns one current address and contact record rather than multiple stale rows. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
---
### BR-VP-022 Affiliate-type classification follows a fixed hierarchy
| Field | Value |
| --- | --- |
| **Statement** | Affiliate-type resolution checks for manual policyholder affiliations first, then wrap and leasing relationships, then subaccount and state-agency relationships, then experience-combination memberships, and finally premium-discount combinations. |
| **Trigger** | `getpolicyaffiliatetype` executes. |
| **Enforcement** | The function evaluates each affiliation family in sequence and returns immediately on the first matching `SELECT ... INTO`. |
| **Outcome** | A policy receives the first matching affiliate classification in the packages explicit precedence order. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
---
### BR-VP-023 Parent-policy resolution depends on the resolved affiliate type
| Field | Value |
| --- | --- |
| **Statement** | Parent-policy resolution chooses the parent lookup strategy from the affiliate type returned by `getpolicyaffiliatetype`, including manual affiliation, relationship, combination, premium-discount, or self-parent fallback paths. |
| **Trigger** | `getparentpolicy` executes. |
| **Enforcement** | The function branches on `v_affil_type` and queries `policy_relationship`, `policy_relationship_policy_num`, `rate_account`, `combo_policy_period`, or the input policy number. |
| **Outcome** | Callers receive the appropriate parent policy number for the resolved affiliation model. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
---
### BR-VP-024 Affiliate total rows are optional and vary by affiliation family
| Field | Value |
| --- | --- |
| **Statement** | Affiliate lists add a single totals row only when `in_include_affil_totals = 'TRUE'`, and the totals label and selection logic vary by affiliate family. |
| **Trigger** | `getpolicyaffiliatelist` executes. |
| **Enforcement** | The procedure builds `v_totals_description` and `v_affil_type`, then includes totals-row `UNION` branches only when `UPPER(in_include_affil_totals) = 'TRUE'`. |
| **Outcome** | Callers can opt into or suppress the family-specific totals row for affiliate displays. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
---
### BR-VP-025 Wrap-up legal-name lookup climbs from member to relationship policy to rate account
| Field | Value |
| --- | --- |
| **Statement** | When a wrap-up member references a different relationship policy, `getwrapuplegalname` first loads that policy legal name and then, if a rate account exists, replaces it with the rate-account policy legal name. |
| **Trigger** | `getwrapuplegalname` executes for a wrap-up member policy. |
| **Enforcement** | The function selects `policy_relationship_policy_num` and `legal_name`, then conditionally selects the relationship policy and finally the `rate_account` policy. |
| **Outcome** | The returned legal name reflects the top-level wrap-up or rate-account owner when available. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
## Operations
The retrieved database source implements the following internal operations.
| Operation | Description | Trigger |
| --- | --- | --- |
| [Retrieve agency of record contact](#retrieve-agency-of-record-contact) | Return the agency, producer, phone, address, and vendor data for the current agent of record. | Caller invokes `getagency`. |
| [Detect policy coverage gaps](#detect-policy-coverage-gaps) | Return gap rows sourced from adjacent policy periods and explicit `POLICY_COVERAGE_GAP` records. | Caller invokes `getgaps`. |
| [Retrieve view-policy summary detail](#retrieve-view-policy-summary-detail) | Return the primary view-policy summary row including entity, NAICS, premium, gap, group, reimbursement, and sales-plan data. | Caller invokes `getviewdetail`. |
| [Build ordered team-assignment rows](#build-ordered-team-assignment-rows) | Return policy-team members plus synthesized office, customer-service-team, agency, and producer rows sorted by display order. | Caller invokes `team_assign_query`. |
| [Retrieve policy-period history with masking rules](#retrieve-policy-period-history-with-masking-rules) | Return policy-period rows while applying assigned-risk masking and current-period selection rules. | Caller invokes `getpolicyperiod` or `getpolicyperiods`. |
| [Resolve policy address rows](#resolve-policy-address-rows) | Return the preferred policy or claim address and contact row by address type or location sequence. | Caller invokes `getpolicyaddress` or `getpolicyaddressbylocseqnum`. |
| [Classify and list affiliated policies](#classify-and-list-affiliated-policies) | Classify a policy affiliation family, then return member and optional totals rows for that family. | Caller invokes `getpolicyaffiliatetype` or `getpolicyaffiliatelist`. |
| [Resolve parent policy number](#resolve-parent-policy-number) | Return the parent policy number that corresponds to the resolved affiliate type. | Caller invokes `getparentpolicy`. |
| [Resolve wrap-up legal name](#resolve-wrap-up-legal-name) | Return the wrap-up owner legal name, escalating to the relationship policy and rate account when necessary. | Caller invokes `getwrapuplegalname`. |
### Retrieve agency of record contact
| Field | Value |
| --- | --- |
| **Description** | Return the agency, producer, phone, address, and vendor data for the current agent of record. |
| **Trigger** | Caller invokes `getagency`. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
### Detect policy coverage gaps
| Field | Value |
| --- | --- |
| **Description** | Return gap rows sourced from adjacent policy periods and explicit `POLICY_COVERAGE_GAP` records. |
| **Trigger** | Caller invokes `getgaps`. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
### Retrieve view-policy summary detail
| Field | Value |
| --- | --- |
| **Description** | Return the primary view-policy summary row including entity, NAICS, premium, gap, group, reimbursement, and sales-plan data. |
| **Trigger** | Caller invokes `getviewdetail`. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
### Build ordered team-assignment rows
| Field | Value |
| --- | --- |
| **Description** | Return policy-team members plus synthesized office, customer-service-team, agency, and producer rows sorted by display order. |
| **Trigger** | Caller invokes `team_assign_query`. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
### Retrieve policy-period history with masking rules
| Field | Value |
| --- | --- |
| **Description** | Return policy-period rows while applying assigned-risk masking and current-period selection rules. |
| **Trigger** | Caller invokes `getpolicyperiod` or `getpolicyperiods`. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
### Resolve policy address rows
| Field | Value |
| --- | --- |
| **Description** | Return the preferred policy or claim address and contact row by address type or location sequence. |
| **Trigger** | Caller invokes `getpolicyaddress` or `getpolicyaddressbylocseqnum`. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
### Classify and list affiliated policies
| Field | Value |
| --- | --- |
| **Description** | Classify a policy affiliation family, then return member and optional totals rows for that family. |
| **Trigger** | Caller invokes `getpolicyaffiliatetype` or `getpolicyaffiliatelist`. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
### Resolve parent policy number
| Field | Value |
| --- | --- |
| **Description** | Return the parent policy number that corresponds to the resolved affiliate type. |
| **Trigger** | Caller invokes `getparentpolicy`. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
### Resolve wrap-up legal name
| Field | Value |
| --- | --- |
| **Description** | Return the wrap-up owner legal name, escalating to the relationship policy and rate account when necessary. |
| **Trigger** | Caller invokes `getwrapuplegalname`. |
| **Source** | Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json` |
## Calling code references
| Class | Method | Source File |
| --- | --- | --- |
| `clsAgency` | `Criteria.RetrieveSQL (`getAgency`)` | `SAIF.Business.View.Policy\clsAgency.vb` |
| `clsMCOCollection` | `Criteria.RetrieveSQL (`getmco`)` | `SAIF.Business.View.Policy\clsMCOCollection.vb` |
| `clsPolicyMCOCollection` | `Criteria.RetrieveSQL (`getmco`)` | `SAIF.Business.View.Policy\clsPolicyMCOCollection.vb` |
| `clsAffiliatedPolicyCollection` | `Criteria.RetrieveSQL (`getpolicyaffiliatelist`)` | `SAIF.Business.View.Policy\clsAffiliatedPolicyCollection.vb` |
| `clsPolicyAddress` | `Criteria.RetrieveSQL (`getPolicyAddress`)` | `SAIF.Business.View.Policy\clsPolicyAddress.vb` |
| `clsPolicyAddress` | `ad hoc method (`getPolicyAddressByLocSeqNum`)` | `SAIF.Business.View.Policy\clsPolicyAddress.vb` |
| `clsPolicyData` | `ad hoc method (`getgaps`)` | `SAIF.Business.View.Policy\clsPolicyData.vb` |
| `clsPolicyData` | `ad hoc method (`getviewdetail`)` | `SAIF.Business.View.Policy\clsPolicyData.vb` |
| `clsPolicyData` | `obsolete ad hoc method (`getParentPolicy`)` | `SAIF.Business.View.Policy\clsPolicyData.vb` |
| `clsPolicyData` | `obsolete ad hoc method (`getpolicyaffiliatetype`)` | `SAIF.Business.View.Policy\clsPolicyData.vb` |
| `clsPolicyData` | `ad hoc method (`getwrapuplegalname`)` | `SAIF.Business.View.Policy\clsPolicyData.vb` |
| `clsPolicyPeriodCollection` | `Criteria.RetrieveSQL (`getPolicyPeriods`)` | `SAIF.Business.View.Policy\clsPolicyPeriodCollection.vb` |
| `clsTeamAssignmentCollection` | `Criteria.RetrieveSQL (`team_assign_query`)` | `SAIF.Business.View.Policy\clsTeamAssignmentCollection.vb` |
## Source Code
Representative package body excerpt for agency and gap retrieval
```sql
PROCEDURE getagency (apolicynum IN NUMBER, recordset IN OUT agency_cursor)
AS
BEGIN
OPEN recordset FOR
SELECT agency.agency_seq_num AS agency_num,
agency.agency_name AS agency_name,
agency.policy_holder_paperless_flag,
AGENT.agent_num AS producer_num,
AGENT.agent_name AS producer_name,
l1.loc_phone AS producer_phone,
l1.loc_addr1,
l1.loc_addr2,
l1.loc_city,
l1.loc_country,
l1.loc_county,
case when agency.statement_addr_loc_seq_num is null then l2.loc_phone else la.loc_phone end loc_phone,
case when agency.statement_addr_loc_seq_num is null then l2.loc_phone_ext else la.loc_phone_ext end loc_phone_ext,
l1.loc_fax,
l1.loc_state,
l1.loc_zip,
l1.loc_zip4,
agency.vendor_num,
l1.email_addr
FROM AGENT
join agency on agency.agency_seq_num = agent.agency_seq_num
left outer join LOCATION_table l1 on AGENT.mail_addr_loc_seq_num = l1.loc_seq_num
left outer join AGENT a2 on a2.agent_num = AGENT.agency_seq_num
left outer join LOCATION_table l2 on a2.mail_addr_loc_seq_num = l2.loc_seq_num
left outer join LOCATION_table la on agency.statement_addr_loc_seq_num = la.loc_seq_num
WHERE AGENT.agent_num = pk72838_comm_authorization.sp72850_get_agent_of_rec(apolicynum)
AND agent.agent_num <> 0;
END getagency;
PROCEDURE getgaps (apolicynum IN NUMBER, recordset IN OUT gap_cursor)
AS
BEGIN
OPEN recordset FOR
SELECT 'GAP' AS rec_type,
TO_CHAR (NVL (p1.cancel_date, p1.policy_period_exp_date),
'MM/dd/yyyy'
) cancel_date,
TO_CHAR (p2.policy_period_eff_date,
'MM/dd/yyyy'
) policy_period_eff_date,
TO_CHAR (p2.policy_period_eff_date, 'yyyyMMdd') sort_date
FROM policy_period_all p1, policy_period_all p2
WHERE p1.policy_num = apolicynum
AND NVL (p1.cancel_date, p1.policy_period_exp_date) <>
p2.policy_period_eff_date
-- exclude flat cancel period and pms prospect periods
AND p1.policy_period_status_code <> 'FC'
AND p1.policy_period_status_code <> 'PPROS'
AND p1.policy_period_eff_date <>
(SELECT MAX (policy_period_eff_date)
FROM policy_period_all
WHERE policy_num = p1.policy_num
AND policy_period_status_code <> 'FC'
AND policy_period_status_code <> 'PPROS')
AND p1.policy_num = p2.policy_num
AND p2.policy_period_eff_date =
(SELECT MIN (policy_period_eff_date)
FROM policy_period_all
WHERE policy_num = p1.policy_num
AND policy_period_eff_date >
p1.policy_period_eff_date
AND policy_period_status_code <> 'FC'
AND policy_period_status_code <> 'PPROS')
UNION
SELECT 'GAP', TO_CHAR (gap_eff_date, 'MM/dd/yyyy'),
TO_CHAR (gap_exp_date, 'MM/dd/yyyy'),
TO_CHAR (gap_eff_date, 'yyyyMMdd') sort_date
FROM policy_coverage_gap
WHERE policy_num = apolicynum
ORDER BY 4 DESC;
END getgaps;
```
Representative package body excerpt for view detail and team assignments
```sql
PROCEDURE getviewdetail (
apolicynum IN NUMBER,
recordset IN OUT viewpolicydetail_cursor
)
AS
BEGIN
OPEN recordset FOR
SELECT p.policy_num, e.code_descr,
--obsolete
s.code_descr as sic_descr,
--10/29/2012 jertod ADDED
n.code_descr as naics_descr,
CASE source_system
WHEN 'WCIS'
THEN sp72658_get_gov_class
(p.policy_num,
ppa.policy_period_eff_date
)
WHEN 'PMS'
THEN sp73029_get_nonwcis_gov_class
(p.policy_num,
ppa.policy_period_eff_date
)
ELSE 'NONE'
END payroll_class,
NVL (p.current_balance, 0) current_balance, p.wcd_num,
--obsolete
p.sic_code as sic_code,
--10/29/2012 jertod ADDED
p.naics_code, p.ncci_risk_id, p.interstate_risk_id_flag,
b.feid_num, NVL (p.premium_estimate, 0) AS premium_estimate,
TO_CHAR (p.policy_orig_eff_date,
'MM/dd/yyyy'
) AS policy_orig_eff_date,
TO_CHAR
(sp72814_get_cont_cov_date (p.policy_num),
'MM/dd/yyyy'
) AS policy_cont_cov_date,
DECODE (getgapindicator (p.policy_num),
0, '',
'- GAP'
) AS gap_in_cov,
ga.code_descr AS group_name, gt.code_descr AS group_type,
NVL (g.chapter_code, ' ') AS group_chapter,
ppa.five_reimb_flag, ppa.five_eff_date, ppa.five_exp_date,
ppa.policy_period_exp_date
--################################################
--, ppa.PSC
--06/09/2006 JERTOD added to mask data from RF status policies
,
CASE ppa.psc
WHEN 'RF'
THEN ''
ELSE CASE pc.code
WHEN 'RETRO'
THEN (SELECT rptc.code_descr_short
|| ' Retro'
FROM retro_plan_policy_period rppp JOIN retro_plan_period rpp
ON (rppp.retro_plan_period_seq_num =
rpp.retro_plan_period_seq_num
)
JOIN retro_rating_plan rrp
ON (rpp.retro_rating_plan_seq_num =
rrp.retro_rating_plan_seq_num
)
JOIN retro_payment_type_code rptc
ON (rrp.retro_payment_type_code = rptc.code
)
WHERE rppp.policy_num = p.policy_num
AND rppp.policy_period_eff_date =
ppa.policy_period_eff_date)
ELSE pc.code_descr
END
END sales_plan_name
,
NVL
((SELECT DISTINCT ('Y')
FROM POLICY p2 JOIN er_combination_policy ecp
ON (p2.policy_num = ecp.policy_num)
WHERE ecp.er_combination_seq_num IN (
SELECT ecp2.er_combination_seq_num
FROM er_combination_policy ecp2
WHERE ecp2.policy_num =
p.policy_num)
AND p2.policy_relationship_code = 'WRAP'),
'N'
) combo_wrap_sub_flag,
p.vendor_num,
b.web_addr
FROM POLICY p,
entity_code e,
business b,
policy_period_group_assoc g,
group_association ga,
group_type_code gt,
--obsolete
sic_code s,
naics_code n,
plan_code pc,
(SELECT ppm.policy_num
, pp.policy_period_status_code psc
,
ppm.policy_period_eff_date,
TO_CHAR
(NVL
(pp.policy_period_exp_date,
ppnw.policy_period_exp_date
),
'MM/dd/yyyy'
) policy_period_exp_date,
ppm.cancel_date, -->3/7/2012 added a table clarifier
ppm.policy_sfx,
NVL (pp.plan_code, ppnw.plan_code) plan_code,
DECODE
(NVL (pp.claims_reimbursement_flag,
ppnw.claims_reimb_flag
),
'Y', 1,
0
) AS five_reimb_flag,
TO_CHAR
(NVL (pp.claims_reimb_eff_date,
ppnw.claims_reimb_eff_date
),
'MM/dd/yyyy'
) AS five_eff_date,
TO_CHAR
(NVL (pp.claims_reimb_exp_date,
ppnw.claims_reimb_exp_date
),
'MM/dd/yyyy'
) AS five_exp_date,
ppm.source_system
FROM policy_period_master ppm LEFT OUTER JOIN policy_period pp
ON ( ppm.policy_num = pp.policy_num
AND ppm.policy_period_eff_date =
pp.policy_period_eff_date
)
LEFT OUTER JOIN policy_period_non_wcis ppnw
ON ( ppm.policy_num = ppnw.policy_num
AND ppm.policy_period_eff_date =
ppnw.policy_period_eff_date
)
WHERE ppm.policy_num = apolicynum
AND ppm.policy_period_eff_date =
(
SELECT MIN( pdate) FROM
(SELECT MAX (policy_period_eff_date) pdate
FROM policy_period_master
WHERE policy_num = apolicynum
AND policy_period_eff_date <= TRUNC (SYSDATE)
UNION
SELECT MAX (policy_period_eff_date) pdate
FROM policy_period_master
WHERE policy_num = apolicynum ) )
) ppa
WHERE p.policy_num = apolicynum
AND p.policy_num = b.policy_num(+) and nvl(b.primary_bus_flag, 'Y') = 'Y'
AND b.entity_code = e.code(+)
AND p.policy_num = ppa.policy_num
AND ppa.policy_num = g.policy_num(+)
AND ppa.policy_period_eff_date = g.policy_period_eff_date(+)
/* JERTOD CHANGED TO INLCUDE FUTURE TERMINATING GROPS
AND g.group_mbrshp_exp_date(+) IS NULL*/
AND SYSDATE < NVL (g.group_mbrshp_exp_date(+), SYSDATE + 1)
AND g.group_association_code = ga.code(+)
AND ga.group_type_code = gt.code(+)
AND p.sic_code = s.code(+)
--obsolete
--10/29/2012 JERTOD added
AND p.naics_code=n.code(+)
AND ppa.plan_code = pc.code(+);
END getviewdetail;
PROCEDURE team_assign_query (
apolicynum IN NUMBER,
recordset IN OUT team_assign_cursor
)
AS
BEGIN
OPEN recordset FOR
SELECT CASE
WHEN user_alias.user_name IS NOT NULL
THEN NVL
((SELECT employee_status_code
FROM wcis_employee
WHERE user_name = user_alias.user_name),
w.employee_status_code
)
ELSE w.employee_status_code
END employee_status_code,
p.policy_num,
NVL (user_alias.user_name, p.user_name) user_name,
w.employee_first_name
|| DECODE (w.employee_initial,
'', ' ',
' ' || w.employee_initial || ' '
)
|| w.employee_last_name AS full_name,
w.phone_num, w.phone_ext, t.code, t.code_descr,
t.screen_order + 10 AS screen_order
FROM policy_team_function p LEFT OUTER JOIN wcis_employee w
ON (trim(p.user_name) = trim(w.user_name))
LEFT OUTER JOIN team_function_code t
ON (p.team_function_code = t.code)
LEFT OUTER JOIN user_alias
ON ( RTRIM (p.user_name) =
RTRIM (user_alias.user_alias_name)
AND RTRIM (p.team_function_code) =
RTRIM (user_alias.alias_code)
)
WHERE p.policy_num = apolicynum AND screen_order > 0
-- get the office
UNION
SELECT 'OTHER', l.policy_num, l.office_code, o.code_descr, ' ',
' ', 'Office', 'Office', 1
FROM POLICY l, office_code o
WHERE l.policy_num = apolicynum AND l.office_code = o.code
UNION
-- get the customer service team
SELECT 'OTHER', p.policy_num, null, c.code_descr, null,null,
'CST', 'Customer Service Team', 2
FROM customer_service_team_code c, POLICY p
WHERE p.customer_service_team_code = c.code
AND p.policy_num = apolicynum
UNION
-- get the agency name and phone #
SELECT 'OTHER', apolicynum, TO_CHAR (agency.agency_seq_num),
agency.agency_name AS full_name,
case when agency.statement_addr_loc_seq_num is null then l2.loc_phone else la.loc_phone end phone_num,
case when agency.statement_addr_loc_seq_num is null then l2.loc_phone_ext else la.loc_phone_ext end phone_ext,
'Agency' code,
'Agency' code_descr, 3
FROM AGENT
join agency on agency.agency_seq_num = agent.agency_seq_num
left outer join location_table la on agency.statement_addr_loc_seq_num = la.loc_seq_num
left outer join AGENT a2 on agent.agency_seq_num = a2.agent_num
left outer join LOCATION_table l2 on a2.mail_addr_loc_seq_num = l2.loc_seq_num
WHERE AGENT.agent_num = pk72838_comm_authorization.sp72850_get_agent_of_rec(apolicynum)
AND agent.agent_num <> 0
UNION
-- get the producer name and phone #
-- this sql is dependent on wcis release 7.1.1
SELECT 'OTHER', apolicynum, TO_CHAR (AGENT.agent_num),
AGENT.agent_name AS full_name, l1.loc_phone phone_num,
l1.loc_phone_ext phone_ext, 'Producer' code,
'Producer' code_descr, 4
FROM AGENT
left outer join LOCATION_table l1 on AGENT.mail_addr_loc_seq_num = l1.loc_seq_num
WHERE AGENT.agent_num = pk72838_comm_authorization.sp72850_get_agent_of_rec(apolicynum)
AND agent.agent_num <> 0
ORDER BY 9;
END team_assign_query;
```
Representative package body excerpt for policy periods and addresses
```sql
PROCEDURE getpolicyperiods (
apolicynum IN NUMBER,
atopnpolper IN NUMBER,
recordset IN OUT ref_cursor
)
AS
vretrieveall NUMBER := 0;
BEGIN
IF atopnpolper > 0
THEN
vretrieveall := atopnpolper;
END IF;
OPEN recordset FOR
SELECT *
FROM (SELECT p.policy_num,
TO_CHAR (p.policy_period_eff_date,
'MM/dd/yyyy'
) AS pp_eff_date,
TO_CHAR (p.policy_period_exp_date,
'MM/dd/yyyy'
) AS pp_exp_date,
TO_CHAR
(NVL (p.cancel_date,
(SELECT cancel_date
FROM policy_period_all
WHERE policy_num = p.policy_num
AND policy_period_eff_date =
p.policy_period_exp_date
AND cancel_date =
policy_period_eff_date)
),
'MM/dd/yyyy'
) AS cancel_date,
'WCIS' AS source_system, 'WCIS' AS source_system_code, p.financial_system_code,
--###########################################
--added 6/9/2006 by JERTOD TO MASK FOR RF POLICIES
--11/7/11 valrey updated masking logic to add check if info page printed ,
CASE p.policy_period_status_code
WHEN 'RF' then
case SP72347_INFO_PAGE_PRINTED_YN(p.policy_num,p.policy_period_eff_date)
when 'Y'
then p.risk_type_code
else
''
end
ELSE p.risk_type_code
END AS risk_type_code
--, p.risk_type_code <-- ELIMINATED
--#################################################
,
p.payroll_report_frequency_code,
TO_CHAR
(p.anniversary_rate_date,
'MM/dd/yyyy'
) AS anniversary_rate_date
--########### JERTOD ADDED 6/9/2006 TO MASK FOR RF POLICIES
,
TO_CHAR
(p.rating_effective_date,
'MM/dd/yyyy'
) AS rating_effective_date
,
--valrey 11/7/11 replaced masking for RF to check if the infopage has printed
CASE p.policy_period_status_code
WHEN 'RF' then
case SP72347_INFO_PAGE_PRINTED_YN(p.policy_num,p.policy_period_eff_date)
when 'Y'
then pc.code_descr
else
''
end
ELSE CASE pc.code
WHEN 'RETRO'
THEN (SELECT rptc.code_descr_short
|| ' Retro'
FROM retro_plan_policy_period rppp JOIN retro_plan_period rpp
ON (rppp.retro_plan_period_seq_num =
rpp.retro_plan_period_seq_num
)
JOIN retro_rating_plan rrp
ON (rpp.retro_rating_plan_seq_num =
rrp.retro_rating_plan_seq_num
)
JOIN retro_payment_type_code rptc
ON (rrp.retro_payment_type_code = rptc.code
)
WHERE rppp.policy_num = p.policy_num
AND rppp.policy_period_eff_date =
p.policy_period_eff_date)
ELSE
pc.code_descr
END
END plan_code,
--############################################################
p.policy_period_eff_date, r.rating_plan_factor,
r.rating_plan_code
|| DECODE (tentative_rating_flag, 'Y', '*', '')
PROCEDURE getpolicyaddress (
inpolicynum IN NUMBER,
inaddresstype IN VARCHAR2,
incontacttype IN VARCHAR2,
ingetmailaddrwhennotfound IN VARCHAR2,
recordset IN OUT ref_cursor
)
AS
v_addr_cnt NUMBER := 0;
v_addr_type VARCHAR2 (5);
BEGIN
v_addr_type := inaddresstype;
BEGIN
IF inaddresstype = 'CLM'
THEN
SELECT COUNT (*)
INTO v_addr_cnt
FROM LOCATION l
WHERE l.policy_num = inpolicynum
AND l.location_code = v_addr_type
AND l.loc_exp_date IS NULL
AND l.loc_eff_date =
(SELECT MAX (l2.loc_eff_date)
FROM LOCATION l2
WHERE l2.policy_num = inpolicynum
AND l2.location_code = v_addr_type
AND l2.loc_exp_date IS NULL);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_addr_cnt := 0;
END;
IF ingetmailaddrwhennotfound = 'Y'
THEN
IF inaddresstype = 'CLM' AND v_addr_cnt = 0
THEN
v_addr_type := 'POL';
END IF;
END IF;
BEGIN
OPEN recordset FOR
SELECT pol.legal_name AS company_name,
DECODE (RTRIM (c.contact_first_name),
NULL, 'Policyholder',
RTRIM (c.contact_first_name)
|| ' '
|| DECODE (c.contact_initial,
'', NULL,
' ', NULL,
c.contact_initial || ' '
)
|| RTRIM (c.contact_last_name)
) contact_name,
location_code, l.location_name, loc_eff_date,
loc_exp_date, loc_addr1 address_line1,
loc_addr2 address_line2, loc_addr3 address_line3,
DECODE (loc_city,
NULL, NULL,
loc_city
|| ', '
|| loc_state
|| ' '
|| loc_zip
|| DECODE (loc_zip4,
NULL, NULL,
'-' || loc_zip4
)
) address_line4,
l.loc_seq_num, l.loc_city, l.loc_country, l.loc_state,
l.loc_county, l.loc_zip, l.loc_zip4, l.loc_phone,
l.loc_phone_ext, l.loc_fax, l.primary_location_flag,
c.contact_type_code, c.contact_first_name,
c.contact_initial, c.contact_last_name, c.contact_title,
c.email_addr contact_email_addr, c.contact_phone,
c.contact_phone_ext, l.email_addr loc_email_addr
FROM LOCATION l, contact c, POLICY pol
WHERE pol.policy_num = inpolicynum
AND pol.policy_num = l.policy_num
AND l.location_code = v_addr_type
AND l.loc_seq_num = c.loc_seq_num(+)
AND c.contact_type_code(+) = incontacttype
AND c.contact_exp_date(+) IS NULL
AND (v_addr_type = 'CLM' OR l.loc_eff_date =
(SELECT MAX (l2.loc_eff_date)
FROM LOCATION l2
WHERE l2.policy_num = inpolicynum
AND l2.location_code = v_addr_type
AND NVL(l2.loc_exp_date, SYSDATE) <> l2.loc_eff_date))
AND NVL(l.LOC_EXP_DATE,SYSDATE + 1) > SYSDATE
AND ROWNUM <=1
ORDER BY loc_eff_date DESC;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
END getpolicyaddress;
PROCEDURE getpolicyaddressbylocseqnum (
inlocationseqnum IN NUMBER,
recordset IN OUT ref_cursor
)
AS
BEGIN
OPEN recordset FOR
SELECT *
FROM (
SELECT
pol.legal_name AS company_name,
DECODE (RTRIM (c.contact_first_name),
NULL, 'Policyholder',
RTRIM (c.contact_first_name)
|| ' '
|| DECODE (c.contact_initial,
'', NULL,
' ', NULL,
c.contact_initial || ' '
)
|| RTRIM (c.contact_last_name)
) contact_name,
location_code, l.location_name, loc_eff_date,
loc_exp_date, loc_addr1 address_line1,
loc_addr2 address_line2, loc_addr3 address_line3,
DECODE (loc_city,
NULL, NULL,
loc_city
|| ', '
|| loc_state
|| ' '
|| loc_zip
|| DECODE (loc_zip4,
NULL, NULL,
'-' || loc_zip4
)
) address_line4,
l.loc_seq_num, l.loc_city, l.loc_country, l.loc_state,
l.loc_county, l.loc_zip, l.loc_zip4, l.loc_phone,
l.loc_phone_ext, l.loc_fax, l.primary_location_flag,
c.contact_type_code, c.contact_first_name,
c.contact_initial, c.contact_last_name, c.contact_title,
c.email_addr contact_email_addr, c.contact_phone,
c.contact_phone_ext, l.email_addr loc_email_addr
FROM LOCATION l, contact c, POLICY pol
WHERE l.loc_seq_num = inLocationSeqNum
AND pol.policy_num = l.policy_num
AND l.loc_seq_num = c.loc_seq_num(+)
AND c.contact_exp_date(+) IS NULL
ORDER BY CASE c.contact_type_code
WHEN 'AUD' THEN 1
WHEN 'PRL' THEN 2
WHEN 'BKP' THEN 3
WHEN 'CPA' THEN 4
WHEN 'ACT' THEN 5
WHEN 'CTR' THEN 6
WHEN 'TAX' THEN 7
ELSE 8
END
, c.contact_eff_date DESC)
WHERE ROWNUM <=1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END getpolicyaddressbylocseqnum;
PROCEDURE getpolicyteamuser (
in_policy_num IN NUMBER,
in_team_function_code IN VARCHAR2,
recordset OUT ref_cursor
)
IS
BEGIN
CASE in_team_function_code
WHEN 'AGT'
THEN
OPEN recordset FOR
SELECT 'Agency' AS mailid, agency.agency_name AS full_name,
case when agency.statement_addr_loc_seq_num is null then l2.loc_phone || NVL2 (l2.loc_phone_ext, 'x' || l2.loc_phone_ext, '')
else la.loc_phone || NVL2 (la.loc_phone_ext, 'x' || la.loc_phone_ext, '') end
AS phone_num
FROM AGENT
join agency on agency.agency_seq_num = agent.agency_seq_num
left outer join LOCATION_table la on agency.statement_addr_loc_seq_num = la.loc_seq_num
left outer join AGENT a2 on a2.agent_num = AGENT.agency_seq_num
left outer join location_table l2 on a2.mail_addr_loc_seq_num = l2.loc_seq_num
WHERE AGENT.agent_num = pk72838_comm_authorization.sp72850_get_agent_of_rec(in_policy_num)
AND agent.agent_num <> 0;
WHEN 'PRD'
```
Representative package body excerpt for affiliation helpers
```sql
FUNCTION getpolicyaffiliatetype (
in_policy_num IN NUMBER,
in_is_policyholder_user IN VARCHAR2
)
RETURN VARCHAR2
IS
v_affiliate_type VARCHAR2 (20) := 'NONAFFILIATED';
BEGIN
-- First check for a manual parent affiliation
-- only when is_policyholder_user = TRUE
IF UPPER (in_is_policyholder_user) = 'TRUE'
THEN
BEGIN
SELECT 'AFFILIATIONPARENT'
INTO v_affiliate_type
FROM policy_relationship
WHERE UPPER (affil_typ_cd) = 'PHP'
AND parent_policy_num = in_policy_num
AND ROWNUM = 1;
RETURN v_affiliate_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
-- Next check for a manual member affiliation
-- only when is_policyholder_user = TRUE
BEGIN
SELECT 'AFFILIATIONMEMBER'
INTO v_affiliate_type
FROM policy_relationship
WHERE UPPER (affil_typ_cd) = 'PHP'
AND mbr_policy_num = in_policy_num;
RETURN v_affiliate_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
END IF;
-- Policy Relationship
-- Next check for a wrapup or leasing parent affiliation
BEGIN
SELECT (CASE
WHEN policy_relationship_code = 'WRAP'
THEN 'WRAPUPPARENT'
WHEN policy_relationship_code = 'LEASE'
THEN 'LEASINGPARENT'
END
)
INTO v_affiliate_type
FROM POLICY
WHERE policy_relationship_code IN ('WRAP', 'LEASE')
AND policy_num = in_policy_num
AND policy_relationship_policy_num = in_policy_num
AND ROWNUM = 1;
RETURN v_affiliate_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
-- Next check for a wrapup or leasing member affiliation
BEGIN
SELECT (CASE
WHEN policy_relationship_code = 'WRAP'
THEN 'WRAPUPMEMBER'
WHEN policy_relationship_code = 'LEASE'
THEN 'LEASINGMEMBER'
END
)
INTO v_affiliate_type
FROM POLICY
WHERE policy_relationship_code IN ('WRAP', 'LEASE')
AND policy_num = in_policy_num
AND policy_relationship_policy_num <> in_policy_num;
RETURN v_affiliate_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
-- Policy Relationship
-- Next check for a subaccount or state agency affiliation
BEGIN
SELECT (CASE
WHEN policy_relationship_code = 'SUBAC'
THEN 'SUBACPARENT'
WHEN policy_relationship_code = 'OREGN'
THEN 'OREGNPARENT'
END
)
INTO v_affiliate_type
FROM POLICY
WHERE policy_relationship_code IN ('SUBAC', 'OREGN')
AND policy_num = in_policy_num
AND policy_relationship_policy_num = in_policy_num
AND ROWNUM = 1;
RETURN v_affiliate_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
-- Next check for a wrapup or leasing member affiliation
BEGIN
SELECT (CASE
WHEN policy_relationship_code = 'SUBAC'
THEN 'SUBACMEMBER'
WHEN policy_relationship_code = 'OREGN'
THEN 'OREGNMEMBER'
END
)
INTO v_affiliate_type
FROM POLICY
WHERE policy_relationship_code IN ('SUBAC', 'OREGN')
AND policy_num = in_policy_num
AND policy_relationship_policy_num <> in_policy_num;
RETURN v_affiliate_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
-- Experience Combination
-- Existing, but moved up in heirarchy
-- Next check for a Combination parent affiliation
BEGIN
SELECT 'COMBOPARENT'
INTO v_affiliate_type
FROM POLICY
WHERE rate_account = in_policy_num AND ROWNUM = 1;
RETURN v_affiliate_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
-- Next check for a Combination member affiliation
BEGIN
SELECT 'COMBOMEMBER'
INTO v_affiliate_type
FROM POLICY
WHERE policy_num = in_policy_num
AND rate_account IS NOT NULL
AND rate_account <> in_policy_num;
RETURN v_affiliate_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
-- VALREY added
-- Premium Discount Combination
-- Next check for a Premium discount combination parent affiliation
BEGIN
SELECT 'PREMIUMPARENT'
INTO v_affiliate_type
FROM POLICY p, combo_policy_period cpp
WHERE p.policy_num = in_policy_num
AND p.policy_num = cpp.policy_num
AND cpp.policy_period_eff_date =
(SELECT MAX (policy_period_eff_date)
FROM policy_period pp
WHERE pp.policy_num = cpp.policy_num)
AND cpp.active_member_flag = 'Y'
AND cpp.min_premium_member_flag = 'Y';
RETURN v_affiliate_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
-- Next check for a Premium combination member affiliation
BEGIN
SELECT 'PREMIUMMEMBER'
INTO v_affiliate_type
FROM POLICY p, combo_policy_period cpp
WHERE p.policy_num = in_policy_num
AND p.policy_num = cpp.policy_num
AND cpp.policy_period_eff_date =
(SELECT MAX (policy_period_eff_date)
FROM policy_period pp
WHERE pp.policy_num = cpp.policy_num)
AND cpp.active_member_flag = 'Y'
AND NVL (cpp.min_premium_member_flag, 'N') = 'N';
RETURN v_affiliate_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
RETURN v_affiliate_type;
END getpolicyaffiliatetype;
/*
This procedure will return the list of affiliated policy numbers
for the passed in policy number and affiliation type.
if the include_affil_totals parm is TRUE then it will include
a totals line at the end of the list.
MODIFIED 09/10/2007 by MARBUR modified GetPolicyAffiliateList to return the agent and agency numbers
MODIFIED 08/03/2010 by MARBUR added an additional select for sub accounts which have no rate account.
*/
PROCEDURE getpolicyaffiliatelist (
in_policy_num IN NUMBER,
in_affiliate_type IN VARCHAR2,
in_include_affil_totals IN VARCHAR2,
recordset OUT ref_cursor
)
IS
v_totals_description VARCHAR2 (50);
v_affil_type VARCHAR2 (10) := '';
BEGIN
-- build the Totals line description
IF UPPER (in_affiliate_type) LIKE 'AFFIL%'
THEN
v_totals_description := '* * * AFFILIATION TOTALS * * *';
v_affil_type := 'PHP';
ELSE
IF UPPER (in_affiliate_type) LIKE 'LEAS%' THEN
v_totals_description := '* * * LEASING COMPANY TOTALS * * *';
v_affil_type := 'LEASE';
ELSE
IF UPPER (in_affiliate_type) LIKE 'WRAP%' THEN
v_totals_description := '* * * AFFILIATION TOTALS * * *';
v_affil_type := 'WRAP';
ELSE
IF UPPER (in_affiliate_type) LIKE 'SUBAC%' THEN
v_totals_description := '* * * COMBINATION TOTALS * * *';
v_affil_type := 'SUBAC';
ELSE
IF UPPER (in_affiliate_type) LIKE 'STATE%' THEN
v_totals_description := '* * * COMBINATION TOTALS * * *';
v_affil_type := 'OREGN';
ELSE
IF UPPER (in_affiliate_type) LIKE 'COMB%' THEN
v_totals_description := '* * * COMBINATION TOTALS * * *';
v_affil_type := 'COMBO';
ELSE
IF UPPER (in_affiliate_type) LIKE 'PREM%' THEN
v_totals_description := '* * * PREMIUM DISCOUNT TOTALS * * *';
v_affil_type := 'PREMIUM';
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
-- Select for the manual affiliation list
IF v_affil_type = 'PHP'
THEN
OPEN recordset FOR
-- get affiliation members
SELECT policy_num || ' - '
|| policy_name AS policy_num_and_name,
TO_CHAR (policy_num) AS policy_key, policy_num,
policy_name
,agt.agent_num, agt.agency_seq_num,
p.policy_status_code
FROM POLICY p JOIN policy_relationship pr
ON ( p.policy_num = mbr_policy_num
OR p.policy_num = parent_policy_num
)
LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
WHERE UPPER (pr.affil_typ_cd) = v_affil_type
AND ( pr.parent_policy_num = in_policy_num
OR pr.parent_policy_num =
(SELECT parent_policy_num
FROM policy_relationship
WHERE mbr_policy_num = in_policy_num)
)
UNION
-- get Totals line
SELECT policy_num
|| ' - '
|| v_totals_description AS policy_num_and_name,
TO_CHAR (policy_num) || '*' AS policy_key, policy_num,
' '
,agt.agent_num, agt.agency_seq_num,
p.policy_status_code
FROM POLICY p JOIN policy_relationship pr
ON (p.policy_num = parent_policy_num)
LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
WHERE UPPER (pr.affil_typ_cd) = v_affil_type
AND ( pr.mbr_policy_num = in_policy_num
OR pr.parent_policy_num = in_policy_num
)
AND UPPER (in_include_affil_totals) = 'TRUE'
AND ROWNUM = 1
ORDER BY 4, 2;
RETURN;
END IF;
-- Select for the leasing company / wrapup list
IF v_affil_type IN ('LEASE', 'WRAP')
THEN
OPEN recordset FOR
-- get leasing company / wrapup members
SELECT policy_num || ' - '
|| policy_name AS policy_num_and_name,
TO_CHAR (policy_num) AS policy_key, policy_num,
policy_name
,agt.agent_num, agt.agency_seq_num,
p.policy_status_code
FROM POLICY p
LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
WHERE p.policy_relationship_code = v_affil_type
AND ( p.policy_relationship_policy_num = in_policy_num
OR p.policy_relationship_policy_num =
(SELECT policy_relationship_policy_num
FROM POLICY
WHERE policy_num = in_policy_num)
)
UNION
-- get the Totals line
SELECT policy_relationship_policy_num
|| ' - '
|| v_totals_description AS policy_num_and_name,
TO_CHAR (policy_relationship_policy_num)
|| '*' AS policy_key,
policy_relationship_policy_num, ' '
,agt.agent_num, agt.agency_seq_num,
p.policy_status_code
FROM POLICY p
LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
WHERE p.policy_relationship_code = v_affil_type
AND ( p.policy_relationship_policy_num = in_policy_num
OR p.policy_relationship_policy_num =
(SELECT policy_relationship_policy_num
FROM POLICY
WHERE policy_num = in_policy_num)
)
AND UPPER (in_include_affil_totals) = 'TRUE'
AND ROWNUM = 1
ORDER BY 4, 2;
RETURN;
END IF;
-- Select for the combination / subaccount / state agencies
IF v_affil_type IN ('COMBO', 'SUBAC', 'OREGN')
THEN
OPEN recordset FOR
-- get combo members
SELECT policy_num || ' - '
|| policy_name AS policy_num_and_name,
TO_CHAR (policy_num) AS policy_key, policy_num,
policy_name
,agt.agent_num, agt.agency_seq_num,
p.policy_status_code
FROM POLICY p
LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
WHERE ( p.rate_account = in_policy_num
OR p.rate_account = (SELECT rate_account
FROM POLICY
WHERE policy_num = in_policy_num)
)
UNION
-- get the Totals line
SELECT rate_account
|| ' - '
|| v_totals_description AS policy_num_and_name,
TO_CHAR (rate_account) || '*' AS policy_key,
rate_account, ' '
,agt.agent_num, agt.agency_seq_num,
p.policy_status_code
FROM POLICY p
LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
WHERE ( p.rate_account = in_policy_num
OR p.rate_account = (SELECT rate_account
FROM POLICY
WHERE policy_num = in_policy_num)
)
AND UPPER (in_include_affil_totals) = 'TRUE'
AND ROWNUM = 1
UNION
-- added additional select for when subaccounts have no rate account number
SELECT policy_num || ' - '
|| policy_name AS policy_num_and_name,
TO_CHAR (policy_num) AS policy_key, policy_num,
policy_name
,agt.agent_num, agt.agency_seq_num,
p.policy_status_code
FROM POLICY p
LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
WHERE p.policy_num = in_policy_num
AND p.rate_account IS NULL
ORDER BY 4, 2;
RETURN;
END IF;
-- Select for the premium discount combination list
IF v_affil_type = 'PREMIUM'
THEN
OPEN recordset FOR
-- get combo members
SELECT cpp2.policy_num
|| ' - '
|| policy_name AS policy_num_and_name
,TO_CHAR (cpp2.policy_num) AS policy_key
,cpp2.policy_num, policy_name, agt.agent_num
,agt.agency_seq_num,
p.policy_status_code
FROM POLICY p
LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
,combo_policy_period cpp
,combo_policy_period cpp2
,policy_status_code psc
WHERE cpp.policy_num = in_policy_num
AND cpp.policy_period_eff_date =
(SELECT MAX (policy_period_eff_date)
FROM policy_period pp
WHERE pp.policy_num = cpp.policy_num)
AND cpp.combo_period_seq_num = cpp2.combo_period_seq_num
AND cpp2.active_member_flag = 'Y'
AND cpp2.policy_num = p.policy_num
and p.POLICY_STATUS_CODE = psc.CODE
UNION
-- get the Totals line
SELECT cpp2.policy_num
|| ' - ' ||
v_totals_description AS policy_num_and_name
, TO_CHAR (cpp2.policy_num) || '*' AS policy_key
,cpp2.policy_num, ' ', agt.agent_num, agt.agency_seq_num,
p.policy_status_code
FROM POLICY p
LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
,combo_policy_period cpp
,combo_policy_period cpp2
WHERE cpp.policy_num = in_policy_num
AND cpp.policy_period_eff_date =
(SELECT MAX (policy_period_eff_date)
FROM policy_period pp
WHERE pp.policy_num = cpp.policy_num)
AND cpp.combo_period_seq_num = cpp2.combo_period_seq_num
AND cpp2.active_member_flag = 'Y'
and cpp2.MIN_PREMIUM_MEMBER_FLAG = 'Y'
AND cpp2.policy_num = p.policy_num
AND UPPER (in_include_affil_totals) = 'TRUE'
AND ROWNUM = 1
ORDER BY 4, 2;
RETURN;
END IF;
END getpolicyaffiliatelist;
-- return the parent policy number for the passed policy.
FUNCTION getparentpolicy (
in_policy_num IN NUMBER,
in_is_policyholder_user IN VARCHAR2
)
RETURN NUMBER
IS
v_parent_policy NUMBER;
v_affil_type VARCHAR (20);
BEGIN
-- get the affiliation type for this policy
v_affil_type :=
getpolicyaffiliatetype (in_policy_num, in_is_policyholder_user);
-- if we get here then we need to query for the parent policy number.
IF v_affil_type = 'AFFILIATIONMEMBER'
THEN
-- get the manual affiliation parent
SELECT parent_policy_num
INTO v_parent_policy
FROM policy_relationship
WHERE mbr_policy_num = in_policy_num;
ELSE
IF v_affil_type IN ('LEASINGMEMBER', 'WRAPUPMEMBER', 'SUBACMEMBER', 'OREGNMEMBER')
THEN
-- get the leasing/wrapup parent
SELECT policy_relationship_policy_num
INTO v_parent_policy
FROM POLICY
WHERE policy_num = in_policy_num;
ELSE
IF v_affil_type = 'COMBOMEMBER'
THEN
-- get the combo parent (rate account)
SELECT rate_account
INTO v_parent_policy
FROM POLICY
WHERE policy_num = in_policy_num;
ELSE
IF v_affil_type = 'PREMIUMMEMBER'
THEN
-- get the minimum premium policy in the combo_policy_period
SELECT cpp2.policy_num
INTO v_parent_policy
FROM combo_policy_period cpp, combo_policy_period cpp2
WHERE cpp.policy_num = in_policy_num
AND cpp.policy_period_eff_date =
(SELECT MAX (policy_period_eff_date)
FROM policy_period pp
WHERE pp.policy_num = cpp.policy_num)
AND cpp.combo_period_seq_num = cpp2.combo_period_seq_num
AND cpp2.active_member_flag = 'Y'
AND cpp2.min_premium_member_flag = 'Y';
ELSE
-- When non of the above affiliation types
-- then set the parent policy = input policy.
v_parent_policy := in_policy_num;
END IF;
END IF;
END IF;
END IF;
RETURN v_parent_policy;
END getparentpolicy;
FUNCTION sp73111_get_wcis_std_premium
/****************************************************************
*Procedure Title : Get WCIS Standard Premium
*Name : SP73111_GET_WCIS_STD_PREMIUM
*Called by : Business Online
*****************************************************************
* Description: This procedure gets the Standardized Standard Premium
* based on the 9/8/07 New Standard Premium Project.
*
* THIS LOGIC IS ALSO USED IN BDS. IF YOU CHANGE THIS
* YOU MUST CORRODINATE WITH THE BDS TEAM.
*
* It gets the max premium history set for the policy
* and policy period passed in. It uses the Prem_Summary
* view to define the correct set.
*
* Maintenance Log:
* Name CSR Date Description
* John Frey 09/08/2007 Initial Release
******************************************************************/
(
in_policy_num IN NUMBER,
in_pol_per_eff_date IN DATE
)
RETURN NUMBER
IS
BEGIN
RETURN pk73059_prem_hist_summary.get_standard_premium(
pk73059_prem_hist_summary.get_max_ph_set_id(in_policy_num,in_pol_per_eff_date));
END sp73111_get_wcis_std_premium;
/* Formatted on 4/23/2020 4:26:52 PM (QP5 v5.256.13226.35510) */
FUNCTION getwrapuplegalname (in_policy_num IN NUMBER)
RETURN VARCHAR2
IS
v_wrapup_legal_name VARCHAR2 (80);
v_rel_policy_num NUMBER;
v_rate_account NUMBER;
BEGIN
BEGIN
SELECT policy_relationship_policy_num, legal_name
INTO v_rel_policy_num, v_wrapup_legal_name
FROM policy
WHERE policy_relationship_code = 'WRAP' AND policy_num = in_policy_num;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
-- Are the policy_num and Policy_Relationship_policy_num different
IF v_rel_policy_num IS NOT NULL AND in_policy_num <> v_rel_policy_num
THEN
BEGIN
SELECT rate_account, legal_name
INTO v_rate_account, v_wrapup_legal_name
FROM policy
WHERE policy_num = v_rel_policy_num;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
IF v_rate_account IS NOT NULL
THEN
BEGIN
SELECT legal_name
INTO v_wrapup_legal_name
FROM policy
WHERE policy_num = v_rate_account;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
END IF;
END IF;
RETURN v_wrapup_legal_name;
END getwrapuplegalname;
```
## Related documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)
## Sources
- Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`
- Calling code: `SAIF.Business.View.Policy\clsAgency.vb`
- Calling code: `SAIF.Business.View.Policy\clsMCOCollection.vb`
- Calling code: `SAIF.Business.View.Policy\clsPolicyMCOCollection.vb`
- Calling code: `SAIF.Business.View.Policy\clsAffiliatedPolicyCollection.vb`
- Calling code: `SAIF.Business.View.Policy\clsPolicyAddress.vb`
- Calling code: `SAIF.Business.View.Policy\clsPolicyData.vb`
- Calling code: `SAIF.Business.View.Policy\clsPolicyPeriodCollection.vb`
- Calling code: `SAIF.Business.View.Policy\clsTeamAssignmentCollection.vb`